/*****************************************************************************
*        描述 :
*             文件描述
*        CLR版本:            3.5
*        创建者 :            蒋 光
*        个人域名:           jiangguang.net.cn
*        命名空间名称:       ECP.DataAccess
*        文件名:             SQLBuilder.cs
*        创建系统时间:       2012/11/2 19:07:00
*        创建年份:           2012
*
/*****************************************************************************/

namespace ECP.DataAccess
{
    using System.Collections.Generic;
    using ECP.DataAccess.Utilities;
    using ECP.Utilities;

    public class SQLBuilder
    {
        public string CommandText;
        private IDbHelper dbHelper;
        private string InsertField;
        private string InsertValue;
        public Dictionary<string, object> Parameter;
        private string SelectSql;
        private DataBaseOperation SqlOperation;
        private string TableName;
        private string UpdateSql;
        private string WhereSql;

        private SQLBuilder()
        {
            this.SqlOperation = DataBaseOperation.Update;
            this.CommandText = string.Empty;
            this.TableName = string.Empty;
            this.InsertValue = string.Empty;
            this.InsertField = string.Empty;
            this.UpdateSql = string.Empty;
            this.SelectSql = string.Empty;
            this.WhereSql = string.Empty;
            this.Parameter = new Dictionary<string, object>();
            this.Parameter = new Dictionary<string, object>();
        }

        public SQLBuilder(IDbHelper dbHelper)
            : this()
        {
            this.dbHelper = dbHelper;
        }

        private void AddParameter(string targetFiled, object targetValue)
        {
            this.Parameter.Add(targetFiled, targetValue);
        }

        private bool Begin(IDbHelper dbHelper, string tableName, DataBaseOperation sqlOperation)
        {
            this.dbHelper = dbHelper;
            this.PrepareCommand();
            this.TableName = tableName;
            this.SqlOperation = sqlOperation;
            return true;
        }

        public bool BeginDelete(string tableName)
        {
            return this.Begin(this.dbHelper, tableName, DataBaseOperation.Delete);
        }

        public bool BeginInsert(string tableName)
        {
            return this.Begin(this.dbHelper, tableName, DataBaseOperation.Insert);
        }

        public bool BeginSelect(string tableName)
        {
            return this.Begin(this.dbHelper, tableName, DataBaseOperation.Select);
        }

        public bool BeginUpdate(string tableName)
        {
            return this.Begin(this.dbHelper, tableName, DataBaseOperation.Update);
        }

        public int EndDelete()
        {
            return this.Execute();
        }

        public int EndInsert()
        {
            return this.Execute();
        }

        public int EndSelect()
        {
            return this.Execute();
        }

        public int EndUpdate()
        {
            return this.Execute();
        }

        private int Execute()
        {
            if (this.SqlOperation == DataBaseOperation.Select)
            {
                this.CommandText = " SELECT * FROM " + this.TableName + this.WhereSql;
            }
            if (this.SqlOperation == DataBaseOperation.Insert)
            {
                this.InsertField = this.InsertField.Substring(0, this.InsertField.Length - 2);
                this.InsertValue = this.InsertValue.Substring(0, this.InsertValue.Length - 2);
                this.CommandText = " INSERT INTO " + this.TableName + "(" + this.InsertField + ") VALUES(" + this.InsertValue + ") ";
            }
            if (this.SqlOperation == DataBaseOperation.Update)
            {
                this.UpdateSql = this.UpdateSql.Substring(0, this.UpdateSql.Length - 2);
                this.CommandText = " UPDATE " + this.TableName + " SET " + this.UpdateSql + this.WhereSql;
            }
            if (this.SqlOperation == DataBaseOperation.Delete)
            {
                this.CommandText = " DELETE FROM " + this.TableName + this.WhereSql;
            }
            this.dbHelper.GetDbCommand().CommandText = this.CommandText;
            foreach (string str in this.Parameter.Keys)
            {
                this.dbHelper.AttachParameters(this.dbHelper.GetDbCommand(), this.dbHelper.MakeParameters(str, this.Parameter[str]));
            }
            int num = this.dbHelper.GetDbCommand().ExecuteNonQuery();
            this.dbHelper.GetDbCommand().Parameters.Clear();
            this.Parameter.Clear();
            return num;
        }

        private void PrepareCommand()
        {
            this.SqlOperation = DataBaseOperation.Update;
            this.CommandText = string.Empty;
            this.TableName = string.Empty;
            this.InsertValue = string.Empty;
            this.InsertField = string.Empty;
            this.UpdateSql = string.Empty;
            this.WhereSql = string.Empty;
            this.dbHelper.GetDbCommand().Parameters.Clear();
        }

        public void SetDBNow(string targetFiled)
        {
            if (this.SqlOperation == DataBaseOperation.Insert)
            {
                this.InsertField = this.InsertField + targetFiled + ", ";
                this.InsertValue = this.InsertValue + this.dbHelper.GetDBNow() + ", ";
            }
            if (this.SqlOperation == DataBaseOperation.Update)
            {
                string updateSql = this.UpdateSql;
                this.UpdateSql = updateSql + targetFiled + " = " + this.dbHelper.GetDBNow() + ", ";
            }
        }

        public void SetFormula(string targetFiled, string formula)
        {
            string relation = " = ";
            this.SetFormula(targetFiled, formula, relation);
        }

        public void SetFormula(string targetFiled, string formula, string relation)
        {
            if (this.SqlOperation == DataBaseOperation.Insert)
            {
                this.InsertField = this.InsertField + targetFiled + ", ";
                this.InsertValue = this.InsertValue + formula + ", ";
            }
            if (this.SqlOperation == DataBaseOperation.Update)
            {
                string updateSql = this.UpdateSql;
                this.UpdateSql = updateSql + targetFiled + relation + formula + ", ";
            }
        }

        public void SetNull(string targetFiled)
        {
            this.SetValue(targetFiled, null);
        }

        public void SetValue(string targetFiled, object targetValue)
        {
            switch (this.SqlOperation)
            {
                case DataBaseOperation.Insert:
                    this.InsertField = this.InsertField + targetFiled + ", ";
                    if (targetValue != null)
                    {
                        if (targetValue.ToString().Length > 0)
                        {
                            this.InsertValue = this.InsertValue + this.dbHelper.GetParameter(targetFiled) + ", ";
                            this.AddParameter(targetFiled, targetValue);
                        }
                        else
                        {
                            this.InsertValue = this.InsertValue + " Null, ";
                        }
                        return;
                    }
                    this.InsertValue = this.InsertValue + " Null, ";
                    return;

                case DataBaseOperation.Update:
                    if (targetValue != null)
                    {
                        if (targetValue.ToString().Length > 0)
                        {
                            string updateSql = this.UpdateSql;
                            this.UpdateSql = updateSql + targetFiled + " = " + this.dbHelper.GetParameter(targetFiled) + ", ";
                            this.AddParameter(targetFiled, targetValue);
                            return;
                        }
                        this.UpdateSql = this.UpdateSql + targetFiled + " = '', ";
                        return;
                    }
                    this.UpdateSql = this.UpdateSql + targetFiled + " = Null, ";
                    return;
            }
        }

        public string SetWhere(string targetFiled, object[] targetValues)
        {
            if (this.WhereSql.Length == 0)
            {
                this.WhereSql = " WHERE ";
            }
            string whereSql = this.WhereSql;
            this.WhereSql = whereSql + targetFiled + " IN (" + BaseBusinessLogic.ObjectsToList(targetValues) + ")";
            return this.WhereSql;
        }

        public string SetWhere(string[] targetFileds, object[] targetValues)
        {
            string relation = " AND ";
            return this.SetWhere(targetFileds, targetValues, relation);
        }

        public string SetWhere(string targetFiled, object targetValue)
        {
            string relation = " AND ";
            return this.SetWhere(targetFiled, targetValue, relation);
        }

        public string SetWhere(string[] targetFileds, object[] targetValues, string relation)
        {
            for (int i = 0; i < targetFileds.Length; i++)
            {
                this.SetWhere(targetFileds[i], targetValues[i], relation);
            }
            return this.WhereSql;
        }

        public string SetWhere(string targetFiled, object targetValue, string relation)
        {
            if (this.WhereSql.Length == 0)
            {
                this.WhereSql = " WHERE ";
            }
            else
            {
                this.WhereSql = this.WhereSql + relation;
            }
            this.WhereSql = this.WhereSql + targetFiled + " = " + this.dbHelper.GetParameter(targetFiled);
            this.AddParameter(targetFiled, targetValue);
            return this.WhereSql;
        }
    }
}